#include "DeviceService.h"
#include "../db/DBPoll.h"
#include "../util/DateTime.h"
#include "../util/TextUtil.h"

// 数据表名称
#define SQL_DB_NAME_DEVICE	"t_device"

// 建表语句
#define SQL_CREATE_DEVICE \
"CREATE TABLE IF NOT EXISTS `t_device` ("\
"`id` VARCHAR(64) NOT NULL comment '设备编码',"\
"`name` VARCHAR(32) default NULL comment '设备名称',"\
"`rtspUrl` VARCHAR(255) default NULL comment '视频地址',"\
"`username` VARCHAR(32) default NULL comment '设备账号',"\
"`password` VARCHAR(64) default NULL comment '设备密码',"\
"`manufacture` VARCHAR(32) default NULL comment '设备厂家',"\
"`enabled` TINYINT(1) default 1 comment '启用状态',"\
"`frameGap` int default 25 comment '分析帧间隔',"\
"`startTime` VARCHAR(32) default '00:00:00' comment '开始分析时间',"\
"`endTime` VARCHAR(32) default '23:59:59' comment '停止分析时间',"\
"`params` TEXT comment '分析参数',"\
"`silence` int default 5 comment '静默周期(不报警周期-秒)',"\
"`createTime` DATETIME default NULL comment '添加时间',"\
"`updateTime` DATETIME default NULL comment '更新时间',"\
"`detectTime` DATETIME default NULL comment '最后检测时间',"\
"`lastImage` VARCHAR(255) default NULL comment '最新图片',"\
"`imageUrl` VARCHAR(255) default NULL comment '图片地址',"\
"`reserver1` VARCHAR(255) default NULL comment '保留字段1',"\
"`reserver2` VARCHAR(255) default NULL comment '保留字段2',"\
"primary key(`id`)"\
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备信息'"

// SQLite建表
#define SQL_CREATE_DEVICE2	\
"CREATE TABLE IF NOT EXISTS `t_device` ("\
"`id` VARCHAR(64) NOT NULL,"\
"`name` VARCHAR(32) default NULL,"\
"`rtspUrl` VARCHAR(255) default NULL,"\
"`username` VARCHAR(32) default NULL,"\
"`password` VARCHAR(64) default NULL,"\
"`manufacture` VARCHAR(32) default NULL,"\
"`enabled` TINYINT(1) default 1,"\
"`frameGap` int default 25,"\
"`startTime` VARCHAR(32) default '00:00:00',"\
"`endTime` VARCHAR(32) default '23:59:59',"\
"`params` TEXT,"\
"`silence` int default 5,"\
"`createTime` DATETIME default NULL,"\
"`updateTime` DATETIME default NULL,"\
"`detectTime` DATETIME default NULL,"\
"`lastImage` VARCHAR(255) default NULL,"\
"`imageUrl` VARCHAR(255) default NULL,"\
"`reserver1` VARCHAR(255) default NULL,"\
"`reserver2` VARCHAR(255) default NULL,"\
"primary key(`id`)"\
")"

// 新增语句
#define SQL_INSERT_DEVICE \
"insert into t_device(`id`, `name`, `rtspUrl`, `username`, `password`,"\
"`manufacture`, `enabled`, `frameGap`, `startTime`, `endTime`, `params`,"\
"`silence`, `createTime`, `updateTime`)"\
"values('%s', '%s', '%s', '%s', '%s', '%s', %d, %d, '%s', '%s', '%s', %d, '%s', '%s')"

// 删除语句					  
#define SQL_DELETE_DEVICE \
"delete from t_device where `id`='%s'"

// 查询语句					  
#define SQL_QUERY_DEVICE \
"select * from t_device where `id`='%s'"

// 分页查询					  
#define SQL_QUERY_DEVICE_PAGE \
"select * from t_device where 1=1"

// 查询数量					  
#define SQL_QUERY_DEVICE_COUNT \
"select count(*) from t_device where 1=1"

// 更新语句					  
#define SQL_UPDATE_DEVICE \
"update t_device set `name`='%s',`rtspUrl`='%s',`username`='%s',"\
"`password`='%s',`manufacture`='%s',`enabled`=%d,`frameGap`=%d,"\
"`startTime`='%s', `endTime`='%s', `params`='%s', `silence`=%d,"\
"`updateTime`='%s' where `id`='%s'"

// 更新图片				  
#define SQL_UPDATE_DEV_IMG \
"update t_device set `lastImage`='%s',`imageUrl`='%s',`detectTime`='%s' where `id`='%s'"

// 更新参数		  
#define SQL_UPDATE_DEV_PARAM \
"update t_device set `params`='%s' where `id`='%s'"


DeviceService::DeviceService()
{
}

DeviceService::~DeviceService()
{
}

bool DeviceService::Exist()
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		bool ret = db->IsTableExist(SQL_DB_NAME_DEVICE);
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool DeviceService::Create()
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = db->GetDbType() == 0 ? SQL_CREATE_DEVICE : SQL_CREATE_DEVICE2;
		sql = TextUtil::get_mutable_instance().Ascii2Utf8(sql.c_str());
		bool ret = db->CreateTable(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool DeviceService::Add(DeviceInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_INSERT_DEVICE)
			% item.GetId() % item.GetName() % item.GetRtspUrl()
			% item.GetUsername() % item.GetPassword() % item.GetManufacture()
			% item.GetEnabled() % item.GetFrameGap() % item.GetStartTime()
			% item.GetEndTime() % item.GetParams() % item.GetSilence()
			% item.GetCreateTime().ToNormalString() % item.GetUpdateTime().ToNormalString());

		uint64_t id = db->Insert(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		if (0xffffffff == id)
		{
			return false;
		}
		return true;
	}
	return false;
}

bool DeviceService::Delete(DeviceInfo& item, bool delFromDB)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		bool ret = false;
		if (delFromDB)
		{
			std::string sql = boost::str(boost::format(SQL_DELETE_DEVICE) % item.GetId());
			ret = db->Delete(sql.c_str());
		}

		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

// 查询数量
int DeviceService::Count()
{
	int count = 0;
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_DEVICE_COUNT));
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			if (db->FetchNextRow())
			{
				char* v = db->GetColValue(0);
				count = std::stoi(v);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
	}
	return count;
}

int DeviceService::Count(std::string id, std::string enabled)
{
	int count = 0;
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_DEVICE_COUNT));
		if (!id.empty())
		{
			sql += boost::str(boost::format(" and `id`='%s'") % id);
		}
		if (!enabled.empty())
		{
			sql += boost::str(boost::format(" and `enabled`=%s") % enabled);
		}
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			if (db->FetchNextRow())
			{
				char* v = db->GetColValue(0);
				count = std::stoi(v);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
	}
	return count;
}

// 查找记录
bool DeviceService::FindPage(int pageIndex, int pageSize, std::vector<DeviceInfo>& list)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_DEVICE_PAGE));
		sql += boost::str(boost::format(" limit %d, %d") % pageIndex % pageSize);
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			while (db->FetchNextRow())
			{
				DeviceInfo item;
				ToRecord(db, item);
				list.push_back(item);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool DeviceService::FindPage(int pageIndex, int pageSize, std::string id, std::string enabled, std::vector<DeviceInfo>& list)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_DEVICE_PAGE));
		if (!id.empty())
		{
			sql += boost::str(boost::format(" and `id`='%s'") % id);
		}
		if (!enabled.empty())
		{
			sql += boost::str(boost::format(" and `enabled`=%s") % enabled);
		}
		sql += boost::str(boost::format(" limit %d, %d") % pageIndex % pageSize);
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			while (db->FetchNextRow())
			{
				DeviceInfo item;
				ToRecord(db, item);
				list.push_back(item);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool DeviceService::Find(DeviceInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_DEVICE) % item.GetId());
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			ret = false;
			if (db->FetchNextRow())
			{
				ToRecord(db, item);
				ret = true;
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool DeviceService::Update(DeviceInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_UPDATE_DEVICE)
			% item.GetName() % item.GetRtspUrl()
			% item.GetUsername() % item.GetPassword()
			% item.GetManufacture() % item.GetEnabled()
			% item.GetFrameGap() % item.GetStartTime()
			% item.GetEndTime() % item.GetParams()
			% item.GetSilence() % item.GetUpdateTime().ToNormalString()
			% item.GetId());
		bool ret = db->Update(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool DeviceService::UpdateImage(DeviceInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_UPDATE_DEV_IMG)
			% item.GetLastImage()
			% item.GetImageUrl()
			% item.GetDetectTime().ToNormalString()
			% item.GetId());
		bool ret = db->Update(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool DeviceService::UpdateParam(DeviceInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_UPDATE_DEV_PARAM)
			% item.GetParams()
			% item.GetId());
		bool ret = db->Update(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

void DeviceService::ToRecord(IDbBasePtr db, DeviceInfo& item)
{
	std::string id = "";
	if (db->GetColValue(0))
	{
		id = db->GetColValue(0);
	}
	std::string name = "";
	if (db->GetColValue(1))
	{
		name = db->GetColValue(1);
	}
	std::string rtspUrl = "";
	if (db->GetColValue(2))
	{
		rtspUrl = db->GetColValue(2);
	}
	std::string username = "";
	if (db->GetColValue(3))
	{
		username = db->GetColValue(3);
	}
	std::string password = "";
	if (db->GetColValue(4))
	{
		password = db->GetColValue(4);
	}
	std::string manufacture = "";
	if (db->GetColValue(5))
	{
		manufacture = db->GetColValue(5);
	}
	int enable = 1;
	if (db->GetColValue(6))
	{
		enable = atoi(db->GetColValue(6));
	}
	int frameGap = 0;
	if (db->GetColValue(7))
	{
		frameGap = atoi(db->GetColValue(7));
	}
	std::string startTime = "00:00:00";
	if (db->GetColValue(8))
	{
		startTime = db->GetColValue(8);
	}
	std::string endTime = "23:59:59";
	if (db->GetColValue(9))
	{
		endTime = db->GetColValue(9);
	}
	std::string params = "";
	if (db->GetColValue(10))
	{
		params = db->GetColValue(10);
	}
	int silence = 0;
	if (db->GetColValue(11))
	{
		silence = atoi(db->GetColValue(11));
	}
	std::string ctime = "";
	if (db->GetColValue(12))
	{
		ctime = db->GetColValue(12);
	}
	std::string mctime = "";
	if (db->GetColValue(13))
	{
		mctime = db->GetColValue(13);
	}
	std::string dctime = "";
	if (db->GetColValue(14))
	{
		dctime = db->GetColValue(14);
	}
	std::string lastImage = "";
	if (db->GetColValue(15))
	{
		lastImage = db->GetColValue(15);
	}
	std::string imageUrl = "";
	if (db->GetColValue(16))
	{
		imageUrl = db->GetColValue(16);
	}

	item.SetId(id);
	item.SetName(name);
	item.SetRtspUrl(rtspUrl);
	item.SetUsername(username);
	item.SetPassword(password);
	item.SetManufacture(manufacture);
	item.SetEnabled(enable);
	item.SetFrameGap(frameGap);
	item.SetStartTime(startTime);
	item.SetEndTime(endTime);
	item.SetParams(params);
	item.SetSilence(silence);

	DateTime create_time;
	create_time.From(ctime);
	item.SetCreateTime(create_time);

	DateTime update_time;
	update_time.From(mctime);
	item.SetUpdateTime(update_time);

	DateTime detect_time;
	detect_time.From(dctime);
	item.SetDetectTime(detect_time);

	item.SetLastImage(lastImage);
	item.SetImageUrl(imageUrl);
}
